<?php

/*
 *  Copyright (C) 2011 Dan de la Cruz <ddcruzg at gmail.com>
 *
 *  MAU is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  MAU is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

/**
 * @author Dan de la Cruz <ddcruzg at gmail.com>
 */
import("mau.dao.implement.DB");
import("mau.dao.interface.IMau");
import("mau.clases.*");

class MauDao implements IMau {

    private function rowToUsuario($row, $usuario) {
        $usuario->setId($row['id']);
        $usuario->setFechaCreacion($row['fecha_creacion']);
        $usuario->setLogin($row['login']);
        $usuario->setPassword($row['password']);
        $usuario->setRoleId($row['role_id']);
        $role = $this->getRoleDeUsuario($usuario);
        $usuario->setRole($role);
        return $usuario;
    }

    private function rowToModulo($row, $modulo) {
        $modulo->setId($row['id']);
        $modulo->setDescripcion($row['descripcion']);
        $permisos = $this->getPermisosDelModulo($modulo);
        $modulo->setPermisos($permisos);
        return $modulo;
    }

    private function rowToPermiso($row, $permiso) {
        $permiso->setId($row['id']);
        $permiso->setDescripcion($row['descripcion']);
        $permiso->setLlave($row['llave']);
        $permiso->setValor($row['valor']);
        return $permiso;
    }

    private function rowToRole($row, $role) {
        $role->setId($row['id']);
        $role->setDescripcion($row['descripcion']);
        $modulos = $this->getModulosDeRole($role);
        $role->setModulos($modulos);
        return $role;
    }

    public function getUsuarios() {

        $pdo = DB::getInst()->getPDO();
        $rs = $pdo->query("SELECT * FROM usuario");

        $lista_usuarios = array();

        while ($row = $rs->fetch()) {
            $usuario = new Usuario();
            $lista_usuarios[] = $this->rowToUsuario($row, $usuario);
        }
        return $lista_usuarios;
    }

    public function getUsuarioById($id) {

        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT * FROM usuario WHERE id = ? ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->bindParam(1, $id, PDO::PARAM_INT);
        $pstmt->execute();
        $row = $pstmt->fetchAll(PDO::FETCH_ASSOC);

        $usuario = new Usuario();
        if ($row) {
            $usuario = $this->rowToUsuario($row[0], $usuario);
        }
        return $usuario;
    }

    public function getUsuarioWhere($filtro) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT * FROM usuario WHERE {$filtro} ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->execute();

        $lista_usuarios = array();
        while ($row = $pstmt->fetch()) {
            $usuario = new Usuario();
            $lista_usuarios[] = $this->rowToUsuario($row, $usuario);
        }
        return $lista_usuarios;
    }

    public function setNuevoUsuario($usuario) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $consulta = "INSERT INTO usuario (id,fecha_creacion,login,password,role_id ) 
                VALUES (null,now(),?,?,?)";
            $login = strtolower($usuario->getLogin());
            $password = sha1($usuario->getPassword());
            $role_id = $usuario->getRoleId();
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $login, PDO::PARAM_STR);
            $pstmt->bindParam(2, $password, PDO::PARAM_STR);
            $pstmt->bindParam(3, $role_id, PDO::PARAM_INT);
            $pstmt->execute();
            $id = $pdo->lastInsertId();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }

        return $id;
    }

    public function deleteUsuarioWhere($filtro) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "DELETE FROM usuario WHERE {$filtro} ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->execute();
    }

    public function deleteUsuarioById($id) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "DELETE FROM usuario WHERE id = ?";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->bindParam(1, $id, PDO::PARAM_INT);
        $pstmt->execute();
    }

    public function deleteModuloById($id) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "DELETE FROM modulo WHERE id = ?";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->bindParam(1, $id, PDO::PARAM_INT);
        $pstmt->execute();
    }

    public function deleteModuloWhere($filtro) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "DELETE FROM modulo WHERE {$filtro} ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->execute();
    }

    public function deletePermisoById($id) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "DELETE FROM permiso WHERE id = ?";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->bindParam(1, $id, PDO::PARAM_INT);
        $pstmt->execute();
    }

    public function deletePermisoWhere($filtro) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "DELETE FROM permiso WHERE {$filtro} ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->execute();
    }

    public function deleteRoleById($id) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "DELETE FROM role WHERE id = ?";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->bindParam(1, $id, PDO::PARAM_INT);
        $pstmt->execute();
    }

    public function deleteRoleWhere($filtro) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "DELETE FROM role WHERE {$filtro} ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->execute();
    }

    public function getModuloById($id) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT * FROM modulo WHERE id = ? ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->bindParam(1, $id, PDO::PARAM_INT);
        $pstmt->execute();
        $row = $pstmt->fetchAll(PDO::FETCH_ASSOC);

        $modulo = new Modulo();
        if ($row) {
            $modulo = $this->rowToModulo($row[0], $modulo);
        }
        return $modulo;
    }

    public function getModuloWhere($filtro) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT * FROM modulo WHERE {$filtro} ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->execute();
        $modulos = array();
        while ($row = $pstmt->fetch()) {
            $modulo = new Modulo();
            $modulos[] = $this->rowToModulo($row, $modulo);
        }
        return $modulos;
    }

    public function getModulos() {
        $pdo = DB::getInst()->getPDO();
        $rs = $pdo->query("SELECT * FROM modulo");

        $modulos = array();

        while ($row = $rs->fetch()) {
            $modulo = new Modulo();
            $modulos[] = $this->rowToModulo($row, $modulo);
        }
        return $modulos;
    }

    public function getPermisos() {
        $pdo = DB::getInst()->getPDO();
        $rs = $pdo->query("SELECT * FROM permiso");

        $permisos = array();

        while ($row = $rs->fetch()) {
            $permiso = new Permiso();
            $permisos[] = $this->rowToPermiso($row, $permiso);
        }
        return $permisos;
    }

    public function getPermisoById($id) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT * FROM permiso WHERE id = ? ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->bindParam(1, $id, PDO::PARAM_INT);
        $pstmt->execute();
        $row = $pstmt->fetchAll(PDO::FETCH_ASSOC);

        $permiso = new Permiso();
        if ($row) {
            $permiso = $this->rowToPermiso($row[0], $permiso);
        }
        return $permiso;
    }

    public function getPermisoWhere($filtro) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT * FROM permiso WHERE {$filtro} ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->execute();
        $permisos = array();
        while ($row = $pstmt->fetch()) {
            $permiso = new Permiso();
            $permisos[] = $this->rowToPermiso($row, $permiso);
        }
        return $permisos;
    }

    public function getRoles() {
        $pdo = DB::getInst()->getPDO();
        $rs = $pdo->query("SELECT * FROM role");

        $roles = array();

        while ($row = $rs->fetch()) {
            $role = new Role();
            $roles[] = $this->rowToRole($row, $role);
        }
        return $roles;
    }

    public function getRoleById($id) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT * FROM role WHERE id = ? ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->bindParam(1, $id, PDO::PARAM_INT);
        $pstmt->execute();
        $row = $pstmt->fetchAll(PDO::FETCH_ASSOC);

        $role = new Role();
        if ($row) {
            $role = $this->rowToRole($row[0], $role);
        }
        return $role;
    }

    public function getRoleWhere($filtro) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT * FROM role WHERE {$filtro} ";
        $pstmt = $pdo->prepare($consulta);
        $pstmt->execute();
        $roles = array();
        while ($row = $pstmt->fetch()) {
            $role = new Role();
            $roles[] = $this->rowToRole($row, $role);
        }
        return $roles;
    }

    public function setNuevoModulo($modulo) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $consulta = "INSERT INTO modulo (id,descripcion) VALUES (null,?)";
            $descripcion = $modulo->getDescripcion();
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $descripcion, PDO::PARAM_STR);
            $pstmt->execute();
            $id = $pdo->lastInsertId();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
        return $id;
    }

    public function setNuevoPermiso($permiso) {

        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $consulta = "INSERT INTO permiso (id,descripcion,llave,valor) VALUES (null,?,?,?)";
            $descripcion = $permiso->getDescripcion();
            $llave = $permiso->getLlave();
            $valor = $permiso->getValor();
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $descripcion, PDO::PARAM_STR);
            $pstmt->bindParam(2, $llave, PDO::PARAM_STR);
            $pstmt->bindParam(3, $valor, PDO::PARAM_STR);
            $pstmt->execute();
            $id = $pdo->lastInsertId();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
        return $id;
    }

    public function setNuevoRole($role) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $consulta = "INSERT INTO role (id,descripcion) VALUES (null,?)";
            $descripcion = $role->getDescripcion();
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $descripcion, PDO::PARAM_STR);
            $pstmt->execute();
            $id = $pdo->lastInsertId();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
        return $id;
    }

    public function setModuloARole($modulo, $role) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $consulta = "INSERT INTO role_modulo (role_id,modulo_id) VALUES (?,?)";
            $pstmt = $pdo->prepare($consulta);
            $role_id = $role->getId();
            $modulo_id = $modulo->getId();
            $pstmt->bindParam(1, $role_id, PDO::PARAM_STR);
            $pstmt->bindParam(2, $modulo_id, PDO::PARAM_STR);
            $pstmt->execute();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

    public function deleteModuloDelRole($modulo, $role) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $consulta = "DELETE FROM role_modulo WHERE role_id = ? AND modulo_id = ?";
            $pstmt = $pdo->prepare($consulta);
            $role_id = $role->getId();
            $modulo_id = $modulo->getId();
            $pstmt->bindParam(1, $role_id, PDO::PARAM_INT);
            $pstmt->bindParam(2, $modulo_id, PDO::PARAM_INT);
            $pstmt->execute();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

    public function getModulosDeRole($role) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT m.* FROM role_modulo AS rm
                INNER JOIN modulo AS m ON m.id = rm.modulo_id
                WHERE role_id = ?; ";
        $pstmt = $pdo->prepare($consulta);
        $id_role = $role->getId();
        $pstmt->bindParam(1, $id_role, PDO::PARAM_INT);
        $pstmt->execute();

        $modulos = array();
        while ($row = $pstmt->fetch()) {
            $modulo = new Modulo();
            $modulos[] = $this->rowToModulo($row, $modulo);
        }
        return $modulos;
    }

    public function getRoleDeUsuario($usuario) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT r.* FROM usuario as u
            INNER JOIN role as r ON r.id = u.role_id
            WHERE u.id = ?";
        $pstmt = $pdo->prepare($consulta);
        $usuario_id = $usuario->getId();
        $pstmt->bindParam(1, $usuario_id, PDO::PARAM_INT);
        $pstmt->execute();

        $role = new Role();
        $row = $pstmt->fetch();
        if ($row) {
            $role = $this->rowToRole($row, $role);
        } else {
            $role = false;
        }
        return $role;
    }

    public function getPermisosDelModulo($modulo) {
        $pdo = DB::getInst()->getPDO();
        $consulta = "SELECT p.* FROM modulo_permiso as mp
            INNER JOIN permiso p ON p.id = mp.permiso_id;
            WHERE modulo_id = ?";
        $pstmt = $pdo->prepare($consulta);
        $id_modulo = $modulo->getId();
        $pstmt->bindParam(1, $id_modulo, PDO::PARAM_INT);
        $pstmt->execute();

        $permisos = array();
        while ($row = $pstmt->fetch()) {
            $permiso = new Permiso();
            $permisos[] = $this->rowToPermiso($row, $permiso);
        }
        return $permisos;
    }

    public function setUsuarioARole($usuario_id, $role_id) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $consulta = "UPDATE usuario SET role_id = ? wHERE id = ?";
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $role_id, PDO::PARAM_INT);
            $pstmt->bindParam(2, $usuario_id, PDO::PARAM_INT);
            $pstmt->execute();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

    public function updateModulo($modulo) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $id = $modulo->getId();
            $descripcion = $modulo->getDescripcion();
            $consulta = "UPDATE modulo SET descripcion = ? WHERE id = ? ";
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $descripcion);
            $pstmt->bindParam(2, $id);
            $pstmt->execute();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

    public function updateRole($role) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $id = $role->getId();
            $descripcion = $role->getDescripcion();
            $consulta = "UPDATE role SET descripcion = ? WHERE id = ? ";
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $descripcion);
            $pstmt->bindParam(2, $id);
            $pstmt->execute();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

    public function updatePermiso($permiso) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $descripcion = $permiso->getDescripcion();
            $llave = $permiso->getLlave();
            $valor = $permiso->getValor();
            $id = $permiso->getId();
            $consulta = "UPDATE permiso SET descripcion = ? , llave = ? , valor = ?  WHERE id = ? ";
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $descripcion);
            $pstmt->bindParam(2, $llave);
            $pstmt->bindParam(3, $valor);
            $pstmt->bindParam(4, $id);
            $pstmt->execute();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

    public function updateUsuario($usuario) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $id_usuario = $usuario->getId();
            $login = $usuario->getLogin();
            $password = sha1($usuario->getPassword());
            $role_id = $usuario->getRoleId();

            $consulta = "UPDATE usuario SET  login = ?, password = ? , role_id = ? WHERE id = ? ";
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $login);
            $pstmt->bindParam(2, $password);
            $pstmt->bindParam(3, $role_id);
            $pstmt->bindParam(4, $id_usuario);
            $pstmt->execute();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

    public function setPermisoAModulo($permiso_id, $modulo_id) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();
            $consulta = "INSERT INTO modulo_permiso VALUE (?,?)";
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $modulo_id, PDO::PARAM_INT);
            $pstmt->bindParam(2, $permiso_id, PDO::PARAM_INT);
            $pstmt->execute();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

    public function deletePermisoDelModulo($permiso_id, $modulo_id) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();

            $consulta = "DELETE FROM modulo_permiso WHERE   modulo_id = ? AND permiso_id = ?";
            $pstmt = $pdo->prepare($consulta);
            $pstmt->bindParam(1, $modulo_id, PDO::PARAM_INT);
            $pstmt->bindParam(2, $permiso_id, PDO::PARAM_INT);
            $pstmt->execute();
            $pdo->commit();
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

    public function autenticarUsuario($login, $pass) {
        $pdo = DB::getInst()->getPDO();
        try {
            $pdo->beginTransaction();

            $consulta = "SELECT * FROM usuario WHERE login =  ? AND password = ?";
            $pstmt = $pdo->prepare($consulta);
            $pass = sha1($pass);
            $pstmt->bindParam(1, $login, PDO::PARAM_STR);
            $pstmt->bindParam(2, $pass, PDO::PARAM_STR);
            $pstmt->execute();
            $pdo->commit();
            
            $row = $pstmt->fetchAll(PDO::FETCH_ASSOC);

            $usuario = new Usuario();
            if ($row) {
                $usuario = $this->rowToUsuario($row[0], $usuario);
                return $usuario;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            $pdo->rollback();
            print "Error!: " . $e->getMessage() . "</br>";
        }
    }

}

?>
